#coding=utf-8
import os
import sys

import openpyxl
from openpyxl.styles import Border, Side, Font
from openpyxl.utils import get_column_letter 

thin=Side(border_style="thin", color='000000')
border = Border(left=thin, right=thin, top=thin, bottom=thin)
fill = openpyxl.styles.PatternFill("solid", fgColor="A8A8A8")
normal_font = Font(name='微软雅黑',size = "9")
bold_font = Font(name='微软雅黑',bold=True, size = "9")
netx_color = "C4D79B"
fill_next = openpyxl.styles.PatternFill("solid", fgColor=netx_color)

def set_auto_cell_width(sheet, row_count):
    # i: cell index
    # j:row index
    for column_index in range(1, 6):
        max_cell_width = 1
        for row_index in range(1, row_count + 1):
            cell = sheet.cell(row=row_index,column=column_index).value
            if isinstance(cell, str):
                cell_width = len(cell.encode("utf-8"))
            else:
                cell_width = len(str(cell))
            if max_cell_width < cell_width:
                max_cell_width = cell_width
        k = get_column_letter(column_index)
        sheet.column_dimensions[k].width = max_cell_width
        
def set_style(sheet, row_count):
    for row in sheet['A1:E' + str(row_count)]:
        for cell in row:
            cell.border = border#A5:D6区域单元格设置边框
    for row in range(1, row_count + 1):
        if row % 2 != 0:
            continue
        for column in range(1, 6):
            sheet.cell(row=row,column=column).fill = fill_next
    for column in range(1, 6):
        for row in range(1, row_count + 1):
            sheet.cell(row=row,column=column).font = normal_font
    for column in range(1, 6):
        sheet.cell(row=1,column=column).fill = fill
        sheet.cell(row=1,column=column).font = bold_font
    sheet.sheet_view.topLeftCell = 'A1'
    sheet.freeze_panes = 'C2'
    set_auto_cell_width(sheet, row_count)